﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyThuVien.DTO;
using System.Data;

namespace QuanLyThuVien.DAO
{
    class PhieuMuonDAO
    {
        //phan ha phuong
        public static void LapPhieuMuon(PhieuMuonDTO x)
        {
            NorthwindDataContext QLTV = new NorthwindDataContext();
            PhieuMuon p = new PhieuMuon();
            p.NgayMuon = x.NgayMuon1;
            p.MaDocGia = x.MaDocGia1;
            p.MaNV = x.MaNV1;
            QLTV.PhieuMuons.InsertOnSubmit(p);
            QLTV.SubmitChanges();
        }
        public static int LayMaPhieuMuon()
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = (from pm in cnn.PhieuMuons
                       select pm.MaPhieuMuon).Max();
            return sql;
        }
        public static DataTable LayNgayMuon(int MaDG, int MaSach )
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = from pm in cnn.PhieuMuons
                      join ct in cnn.ChiTietPhieuMuons on pm.MaPhieuMuon equals ct.MaPhieuMuon
                      where pm.MaDocGia == MaDG && ct.MaSach == MaSach
                      select new { ct.MaSach, pm.NgayMuon };
            DataTable dt = new DataTable();
            dt.Columns.Add("MaSach",typeof(int));
            dt.Columns.Add("NgayMuon",typeof(DateTime));
            foreach (var gan in sql)
            {
                dt.Rows.Add(gan.MaSach,gan.NgayMuon);
            }
            return dt;
        }
        //lay ma phieu muon cua cuon sach
        public static int LayMaPM(int MaDG, int Masach)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = (from pm in cnn.PhieuMuons
                       join ct in cnn.ChiTietPhieuMuons on pm.MaPhieuMuon equals ct.MaPhieuMuon
                       where pm.MaDocGia == MaDG && ct.MaSach == Masach
                       select pm.MaPhieuMuon).Max();
            return sql;
 
        }

        // phan cua truyen
        public static DataTable DemTheLoai()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var TatCaSach = from pm in db.PhieuMuons
                            join ctpm in db.ChiTietPhieuMuons on pm.MaPhieuMuon equals ctpm.MaPhieuMuon
                            join s in db.Saches on ctpm.MaSach equals s.MaSach
                            join tl in db.TheLoais on s.MaTheLoai equals tl.MaTheLoai

                            select new
                            {
                                pm.NgayMuon,
                                ctpm.MaSach,
                                pm.MaDocGia,
                                s.TenSach,
                                s.MaTheLoai,
                                tl.TenTheLoai,

                            };
            var ketQua = from tc in TatCaSach
                         group tc by new { tc.MaTheLoai, tc.TenTheLoai } into myGroup
                         select new
                         {
                             myGroup.Key.MaTheLoai,
                             myGroup.Key.TenTheLoai,
                             Count = myGroup.Count()

                         };
            DataTable dt = new DataTable();
            dt.Columns.Add("TenTheLoai", typeof(string));
            dt.Columns.Add("CountTheLoai", typeof(int));
            foreach (var gtri in ketQua)
            {
                dt.Rows.Add(gtri.TenTheLoai, gtri.Count);
            }
            return dt;

        }

        public static DataTable DemTheLoaiTheoThang(int thang)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var TatCaSach = from pm in db.PhieuMuons
                            join ctpm in db.ChiTietPhieuMuons on pm.MaPhieuMuon equals ctpm.MaPhieuMuon
                            join s in db.Saches on ctpm.MaSach equals s.MaSach
                            join tl in db.TheLoais on s.MaTheLoai equals tl.MaTheLoai
                            where pm.NgayMuon.Date.Month == thang
                            select new
                            {
                                pm.NgayMuon,
                                ctpm.MaSach,
                                pm.MaDocGia,
                                s.TenSach,
                                s.MaTheLoai,
                                tl.TenTheLoai,

                            };
            var ketQua = from tc in TatCaSach
                         group tc by new { tc.MaTheLoai, tc.TenTheLoai } into myGroup
                         select new
                         {
                             myGroup.Key.MaTheLoai,
                             myGroup.Key.TenTheLoai,
                             Count = myGroup.Count()

                         };
            DataTable dt = new DataTable();
            dt.Columns.Add("TenTheLoai", typeof(string));
            dt.Columns.Add("CountTheLoai", typeof(int));
            foreach (var gtri in ketQua)
            {
                dt.Rows.Add(gtri.TenTheLoai, gtri.Count);
            }
            return dt;

        }
    }
}
